package com.neuedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.neuedu.db.C3p0Util1;

public class UserDaoImpl implements UserDao {
	Connection conn = null;
	PreparedStatement pstm = null;	
	ResultSet rs = null;
	User u = null;
	List<User> users = null;
	
	@Override
	public boolean add(User u) {				
		try {
			conn = C3p0Util1.getConn();
			String sql = " insert into user(name,pwd,role) values(?,?,?) ";
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, u.getName());
			pstm.setObject(2, u.getPwd());
			pstm.setObject(3, u.getRole());
			return pstm.executeUpdate()>0;			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
	}

	@Override
	public boolean del(int id) {
		try {
			conn = C3p0Util1.getConn();
			String sql = " delete from  user where id=? ";
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, id);
			return pstm.executeUpdate()>0;			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
	}

	@Override
	public boolean modify(User u) {
		try {
			conn = C3p0Util1.getConn();
			String sql = " update user " 
					   + " set name=?, pwd=?, role=? "
					   + " where id = ?";					
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, u.getName());
			pstm.setObject(2, u.getPwd());
			pstm.setObject(3, u.getRole());
			pstm.setObject(4, u.getId());
			return pstm.executeUpdate()>0;			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
	}

	@Override
	public User queryByNamePwd(String name, String pwd) {
		try {
			conn = C3p0Util1.getConn();
			String sql = " select * from user where name=? and pwd=? ";
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, name);
			pstm.setObject(2, pwd);
			
			rs = pstm.executeQuery();
			while(rs.next()) {
				u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setPwd(rs.getString("pwd"));
				u.setRole(rs.getString("role"));
			}
			return u;					
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			C3p0Util1.close(rs);
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
	}

	@Override
	public List<User> queryAll() {		
		try {
			conn = C3p0Util1.getConn();
			String sql = " select * from user ";
			pstm = conn.prepareStatement(sql);	
			rs = pstm.executeQuery();
			
			users = new ArrayList<>();
			while(rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				u.setPwd(rs.getString("pwd"));
				u.setRole(rs.getString("role"));
				users.add(u);
			}
			return users;					
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			C3p0Util1.close(rs);
			C3p0Util1.close(pstm);
			C3p0Util1.close(conn);
		}
	}

}
